package softswitch.service;

import java.util.Date;
import java.util.List;
import javax.ejb.Stateless;
import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import javax.persistence.Query;
import softswitch.model.Provider;
import softswitch.model.CallSummaryOperator;
import softswitch.model.Operator;
import softswitch.model.RouteGroup;


@Stateless
public class CallSummaryOperatorDAO {

	@PersistenceContext(unitName = "persistence")
    private EntityManager em;
		
	
	public CallSummaryOperator getById(Integer id){	
		return (CallSummaryOperator) em.find(CallSummaryOperator.class, id);
	}
	
	public CallSummaryOperator save(CallSummaryOperator callsummaryoperator) {
    	em.persist(callsummaryoperator);
    	return callsummaryoperator;
    }
	
	public CallSummaryOperator update(CallSummaryOperator callsummaryoperator) {
    	return em.merge(callsummaryoperator);
    }
    
	public void delete(Integer id) {
    	em.remove(em.find(CallSummaryOperator.class, id));	
    }
    
    //----------------------------------------------------------------------
	
	public void deleteByDateRange(Date startdate, Date enddate,Operator operator ) {	
		String sql = "DELETE FROM CallSummaryOperator e WHERE e.date BETWEEN :startdate AND :enddate ";
		if(operator!=null){
			sql = sql + " AND e.operator=:operator ";
		}
		Query query = em.createQuery(sql);
		query.setParameter("startdate", startdate);
		query.setParameter("enddate", enddate);
		if(operator!=null){
			query.setParameter("operator", operator);
		}
        query.executeUpdate();   
    }
	
	
    public CallSummaryOperator getByDay(Date startdate, String context, RouteGroup routegroup, Operator operator, Provider provider ) {	

    	String sql = "SELECT e FROM CallSummaryOperator e WHERE e.date=:startdate AND e.context=:context "
    			+ "AND  e.routegroup=:routegroup AND e.operator=:operator AND e.provider=:provider ";
		
		Query query = em.createQuery(sql);
		query.setParameter("startdate", startdate);
		query.setParameter("context", context);
		query.setParameter("routegroup", routegroup);
		query.setParameter("operator", operator);
		query.setParameter("provider", provider);
		
		try{
			return (CallSummaryOperator) query.getSingleResult();
		}
		catch(Exception e){
			return null;
		}

    }
    
 
    @SuppressWarnings("unchecked")
    public List<CallSummaryOperator> listByRange(Date startdate, Date enddate, String context, Operator operator, RouteGroup routegroup, Provider provider) {

        String sql = "SELECT e FROM CallSummaryOperator e WHERE e.date BETWEEN :startdate AND :enddate ";

        if (context != null) {
            sql = sql + " AND e.context=:context ";
        }
        if (operator != null) {
            sql = sql + " AND e.operator=:operator ";
        }
        if (provider != null) {
            sql = sql + " AND e.provider=:provider ";
        }
        if (routegroup != null) {
            sql = sql + " AND e.routegroup=:routegroup ";
        }

        sql = sql + " ORDER BY e.date ASC";
        Query query = em.createQuery(sql);
        query.setParameter("startdate", startdate);
        query.setParameter("enddate", enddate);

        if (context != null) {
            query.setParameter("context", context);
        }
        if (operator != null) {
            query.setParameter("operator", operator);
        }
        if (provider != null) {
            query.setParameter("provider", provider);
        }
        if (routegroup != null) {
            query.setParameter("routegroup", routegroup);
        }

        return query.getResultList();

    }
  
    @SuppressWarnings("unchecked")
    public List<String> listOperatorByRange(Date startdate, Date enddate, String context) {
        List<String> list = null;
        String sql = "select DISTINCT CONCAT(o.name,r.name) from softswitch.CallSummaryOperator c "
                + "join softswitch.Operator o on c.operator_id = o.id "
                + "join softswitch.Routegroup r on c.routegroup_id = r.id "
                + " WHERE c.date BETWEEN ? AND ? AND c.context=?";
        Query query = em.createNativeQuery(sql);
        query.setParameter(1, startdate);
        query.setParameter(2, enddate);
        query.setParameter(3, context);
        list = query.getResultList();
        return list;
    }
    
    
}
